package DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import JDBC.DATABASE;
import VO.User;

public class UserDao{
	/*
	 * 查找用户是否存在
	 */
	public boolean userIsExist(String account1){
		Connection conn=DATABASE.getConnection();

		String sql="select * from users where account=?";//���ָ���û����ѯ�û���Ϣ
		try{
			PreparedStatement ps=conn.prepareStatement(sql);//��ȡPreparedStatement����
			ps.setString(1,account1);
			ResultSet rs=ps.executeQuery();//ִ�в�ѯ��ȡ���
			if(!rs.next()){
				//System.out.print("keyong");
				return true;
			}
			rs.close();
			ps.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DATABASE.close(conn);
		}
		return false;

	}
	/*
	 * 保存用户信息
	 */
	public void saveUser(User user){
		Connection conn=DATABASE.getConnection();
		String sql="insert into users (account,name,tele,password,mail) VALUES(?,?,?,?,?)";
		try{
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1,user.getAccount());
			ps.setString(2,user.getName());
			ps.setString(3,user.getTele());
			ps.setString(4,user.getPassword());
			ps.setString(5,user.getMail());
			ps.executeUpdate();
			ps.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DATABASE.close(conn);
		}		
	}
	/*
	 * 判断用户的账号和密码是否匹配
	 */
	public User login(String account1,String password1){
		User user=null;
		Connection conn=DATABASE.getConnection();

		String sql="select * from users where account=? and password=? ";
		try{
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1,account1);
			ps.setString(2,password1);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				System.out.print("3");
				user=new User();
				user.setAccount(rs.getString("account"));
				user.setName(rs.getString("name"));
				user.setTele(rs.getString("tele"));
				user.setPassword(rs.getString("password"));
				user.setMail(rs.getString("mail"));
			}
			rs.close();
			ps.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DATABASE.close(conn);
		}		
		return user;
	}

/*
 * 查找所有用户信息
 */
	public ArrayList queryAllUserMsg() throws Exception {
		ArrayList users = new ArrayList();
		Connection conn=DATABASE.getConnection();
		String sql = "select * from users";
		ResultSet rs = DATABASE.query(sql);
		try {	

			while (rs.next()) {
				User user = new User();
				user.setAccount(rs.getString("account"));
				user.setName(rs.getString("name"));
				user.setTele(rs.getString("tele"));
				user.setPassword(rs.getString("password"));
				user.setMail(rs.getString("mail"));
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}finally{
			DATABASE.close(conn);
		}
		return users;
	}
	/*
	 * 修改用户信息
	 */
	public void changeUserMsg(String account,String name,String tele,String mail){
		Connection conn=DATABASE.getConnection();
		String sql="update users set NAME='"+name+"',TELE='"+tele+"'," +
				"MAIL='"+mail+"' where ACCOUNT='"+account+"'";
		try{
			PreparedStatement ps=conn.prepareStatement(sql);
			System.out.print("33");

			ps.executeUpdate();
			ps.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DATABASE.close(conn);
		}		
	}
	/*
	 * 统计用户总数
	 */
	public int userTotal() throws SQLException{
		Connection conn=DATABASE.getConnection();//连接数据库
		CallableStatement cs=conn.prepareCall("{call usertotal(?)}"); 
		//调用统计用户总数的存储过程
		cs.registerOutParameter(1,Types.INTEGER); 
		//输出的第一个值为整型
		cs.execute();
		int n=cs.getInt(1);
		System.out.print(cs.getInt(1));
		return n;
	}

	/*
	 * 查找所有用户信息
	 */
	public ArrayList queryAllUser() throws Exception {//查找所有用户信息
		ArrayList users = new ArrayList();
		Connection conn=DATABASE.getConnection();//连接数据库
		CallableStatement cs=conn.prepareCall("{call p_msg.displayuser(?)}"); 
		//调用包的存储过程displayuser
		//TODO
//		cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
		boolean bool = cs.execute();
		/*查询*/
		System.out.println(bool);
		ResultSet rs = (ResultSet)cs.getObject(1); 
		try {	
			while (rs.next()) {
				User user = new User();
				user.setAccount(rs.getString("account"));
				user.setName(rs.getString("name"));
				user.setTele(rs.getString("tele"));
				user.setPassword(rs.getString("password"));
				user.setMail(rs.getString("mail"));
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}finally{
			DATABASE.close(conn);
		}
		return users;
	}
	/*
	 * 根据账号查找用户信息
	 */
	
	public ArrayList userMsg(String account1){
		Connection conn=DATABASE.getConnection();
		ArrayList users = new ArrayList();
		String sql="select * from users where account=?";
		try{
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1,account1);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				User user=new User();
				user.setAccount(rs.getString("account"));
				user.setName(rs.getString("name"));
				user.setTele(rs.getString("tele"));
				user.setMail(rs.getString("mail"));
				users.add(user);
			}
			rs.close();
			ps.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DATABASE.close(conn);
		}
		return users;
	

	}

}
